package lulin.w09.dao;

import lulin.w09.entity.School;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.util.List;

@Repository
public class SchoolDao implements SchoolDaoInterface {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public Long addSchool(School school) {
        String sql = "INSERT INTO school (description, no, remarks) VALUES (?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
            ps.setString(1, school.getDescription());
            ps.setString(2, school.getNo());
            ps.setString(3, school.getRemarks());
            return ps;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    @Override
    public int deleteSchool(Long id) {
        return jdbcTemplate.update("DELETE FROM school WHERE id = ?", id);
    }

    @Override
    public int updateSchool(School school) {
        return jdbcTemplate.update("UPDATE school SET description = ?, no = ?, remarks = ? WHERE id = ?",
                school.getDescription(), school.getNo(), school.getRemarks(), school.getId());
    }

    @Override
    public School getSchool(Long id) {
        return jdbcTemplate.queryForObject("SELECT * FROM school WHERE id = ?",
                new BeanPropertyRowMapper<>(School.class), id);
    }

    @Override
    public List<School> getAllSchools() {
        return jdbcTemplate.query("SELECT * FROM school", new BeanPropertyRowMapper<>(School.class));
    }
}    